<?php

class Model_Group
{

    protected $_table;

    public function getTable()
    {
        if (null === $this->_table) {
            require_once APPLICATION_PATH . '/models/DbTable/Group.php';
            $this->_table = new Model_DbTable_Group;
        }
        return $this->_table;
    }
    
    public function save(array $data)
    {
        $table  = $this->getTable();
        $fields = $table->info(Zend_Db_Table_Abstract::COLS);
        foreach ($data as $field => $value) {
            if (!in_array($field, $fields)) {
                unset($data[$field]);
            }
        }
        return $table->insert($data);
    }
    
    public function fetchEntries()
    {
        return $this->getTable()->fetchAll('1')->toArray();
    }
    public function searchGroups($query, $start, $limit)
    {
        $table = $this->getTable();        
        $db = $table->getAdapter();                
        $query = '%'.$query.'%';
        $select = $db->select()->from(array('g' => 'groups'), array('id', 'group_name' => 'name', 'date_created'))
                                ->join(array('m' => 'markers'), 'm.group_id = g.id', array('marker_id' => 'id', 'marker_name' => 'name', 'description'))
                                ->where('g.name LIKE ?', $query)
                                ->orWhere('m.name LIKE ?', $query)
                                ->orWhere('m.description LIKE ?', $query)
                                ->limit($limit, $start);
        
        $stmt = $db->query($select);        
        $rows = $stmt->fetchAll();
        return $rows;
    }
    public function searchGroupsCount($query){
        $table = $this->getTable();
        $db = $table->getAdapter();
        $query = '%'.$query.'%';
        $sql = 'SELECT COUNT(g.id) 
                FROM groups g JOIN markers m ON g.id = m.group_id 
                WHERE g.name LIKE ? OR m.name LIKE ? OR m.description LIKE ?';
        $stmt = $db->query($sql, array($query, $query, $query));
        return $stmt->fetchColumn(0);
    }
    public function getLast($limit){
        $table = $this->getTable();        
        $db = $table->getAdapter();                        
        $sql = 'SELECT COUNT(m.id) as markers_count, g.id, g.name, g.date_created 
                FROM groups g JOIN markers m ON g.id = m.group_id 
                GROUP BY g.id
                ORDER BY g.date_created
                LIMIT '.$limit;
        $stmt = $db->query($sql);   
        //echo $sql;die();        
        $rows = $stmt->fetchAll();
        return $rows;
        /*
        $table = $this->getTable();        
        $select = $table->select()->order('date_created')->limit($limit, 0);
        return $table->fetchAll($select)->toArray();*/
    }
    public function fetchEntry($id)
    {
        $table = $this->getTable();
        $select = $table->select()->where('id = ?', $id);
        return $table->fetchRow($select)->toArray();
    }
    
    public function saveGroup($name, $user_id){
        $table = $this->getTable();
        $select = $table->select()->where('name = ?', $name);
        $row = $table->fetchRow($select);
        if(isset($row))
            return $row->id;
        $row = $table->createRow();
        $row->name = $name;
        $row->user_id = $user_id;
        $row->date_created = date('Y-m-d H:i:s');
        $row->save();
        return $row->id;
    }
}
